package migrations

import (
	"database/sql"
	"fmt"
	"io/ioutil"
	"path/filepath"
	"sort"
	"strconv"
	"strings"

	"github.com/lib/pq"
	_ "github.com/lib/pq"
)

// Migration 表示一个数据库迁移
type Migration struct {
	Version int
	Name    string
	SQL     string
}

// Migrator 数据库迁移器
type Migrator struct {
	db *sql.DB
}

// NewMigrator 创建新的迁移器
func NewMigrator(db *sql.DB) *Migrator {
	return &Migrator{db: db}
}

// Migrate 执行数据库迁移
func (m *Migrator) Migrate() error {
	// 确保迁移表存在
	if err := m.ensureMigrationTable(); err != nil {
		return fmt.Errorf("failed to ensure migration table: %w", err)
	}

	// 获取所有迁移文件
	migrations, err := m.loadMigrations()
	if err != nil {
		return fmt.Errorf("failed to load migrations: %w", err)
	}

	// 获取已执行的迁移
	appliedMigrations, err := m.getAppliedMigrations()
	if err != nil {
		return fmt.Errorf("failed to get applied migrations: %w", err)
	}

	// 执行未应用的迁移
	for _, migration := range migrations {
		versionStr := fmt.Sprintf("%03d", migration.Version)
		if !appliedMigrations[versionStr] {
			if err := m.applyMigration(migration); err != nil {
				return fmt.Errorf("failed to apply migration %d: %w", migration.Version, err)
			}
			fmt.Printf("Applied migration %d: %s\n", migration.Version, migration.Name)
		}
	}

	return nil
}

// ensureMigrationTable 确保迁移表存在
func (m *Migrator) ensureMigrationTable() error {
	query := `
		CREATE TABLE IF NOT EXISTS schema_migrations (
			version VARCHAR(255) PRIMARY KEY,
			dirty BOOLEAN NOT NULL DEFAULT FALSE,
			created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
		);
	`
	_, err := m.db.Exec(query)
	return err
}

// loadMigrations 加载所有迁移文件
func (m *Migrator) loadMigrations() ([]Migration, error) {
	// 获取当前执行文件的目录
	migrationsDir := "migrations"

	files, err := filepath.Glob(filepath.Join(migrationsDir, "*.sql"))
	if err != nil {
		return nil, err
	}

	var migrations []Migration
	for _, file := range files {
		migration, err := m.loadMigrationFromFile(file)
		if err != nil {
			return nil, fmt.Errorf("failed to load migration from %s: %w", file, err)
		}
		if migration != nil {
			migrations = append(migrations, *migration)
		}
	}

	// 按版本号排序
	sort.Slice(migrations, func(i, j int) bool {
		return migrations[i].Version < migrations[j].Version
	})

	return migrations, nil
}

// loadMigrationFromFile 从文件加载迁移
func (m *Migrator) loadMigrationFromFile(filename string) (*Migration, error) {
	// 从文件名解析版本号
	basename := filepath.Base(filename)
	if !strings.HasSuffix(basename, ".sql") {
		return nil, nil // 跳过非SQL文件
	}

	// 提取版本号 (例如: 001_create_tables.sql -> 1)
	parts := strings.Split(basename, "_")
	if len(parts) < 2 {
		return nil, fmt.Errorf("invalid migration filename format: %s", basename)
	}

	version, err := strconv.Atoi(parts[0])
	if err != nil {
		return nil, fmt.Errorf("invalid version number in filename %s: %w", basename, err)
	}

	// 读取SQL内容
	content, err := ioutil.ReadFile(filename)
	if err != nil {
		return nil, fmt.Errorf("failed to read file %s: %w", filename, err)
	}

	// 提取迁移名称
	name := strings.TrimSuffix(basename, ".sql")

	return &Migration{
		Version: version,
		Name:    name,
		SQL:     string(content),
	}, nil
}

// getAppliedMigrations 获取已应用的迁移
func (m *Migrator) getAppliedMigrations() (map[string]bool, error) {
	query := `SELECT version FROM schema_migrations WHERE dirty = false`
	rows, err := m.db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	applied := make(map[string]bool)
	for rows.Next() {
		var version string
		if err := rows.Scan(&version); err != nil {
			return nil, err
		}
		applied[version] = true
	}

	return applied, rows.Err()
}

// applyMigration 应用单个迁移
func (m *Migrator) applyMigration(migration Migration) error {
	tx, err := m.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// 标记迁移为dirty
	versionStr := fmt.Sprintf("%03d", migration.Version)
	_, err = tx.Exec(
		"INSERT INTO schema_migrations (version, dirty) VALUES ($1, true) ON CONFLICT (version) DO UPDATE SET dirty = true",
		versionStr,
	)
	if err != nil {
		return err
	}

	// 执行迁移SQL
	_, err = tx.Exec(migration.SQL)
	if err != nil {
		// 如果是PostgreSQL错误，尝试提供更好的错误信息
		if pqErr, ok := err.(*pq.Error); ok {
			return fmt.Errorf("PostgreSQL error in migration %d: %s (Code: %s, Detail: %s)",
				migration.Version, pqErr.Message, pqErr.Code, pqErr.Detail)
		}
		return err
	}

	// 标记迁移为成功
	_, err = tx.Exec(
		"UPDATE schema_migrations SET dirty = false WHERE version = $1",
		versionStr,
	)
	if err != nil {
		return err
	}

	return tx.Commit()
}

// GetCurrentVersion 获取当前数据库版本
func (m *Migrator) GetCurrentVersion() (int, error) {
	var version string
	err := m.db.QueryRow(
		"SELECT version FROM schema_migrations WHERE dirty = false ORDER BY version DESC LIMIT 1",
	).Scan(&version)

	if err == sql.ErrNoRows {
		return 0, nil // 没有迁移记录
	}
	if err != nil {
		return 0, err
	}

	return strconv.Atoi(version)
}

// GetMigrationStatus 获取迁移状态
func (m *Migrator) GetMigrationStatus() ([]MigrationStatus, error) {
	// 获取所有迁移文件
	migrations, err := m.loadMigrations()
	if err != nil {
		return nil, err
	}

	// 获取已应用的迁移
	appliedMigrations, err := m.getAppliedMigrations()
	if err != nil {
		return nil, err
	}

	var status []MigrationStatus
	for _, migration := range migrations {
		versionStr := fmt.Sprintf("%03d", migration.Version)
		applied := appliedMigrations[versionStr]

		status = append(status, MigrationStatus{
			Version: migration.Version,
			Name:    migration.Name,
			Applied: applied,
		})
	}

	return status, nil
}

// MigrationStatus 迁移状态
type MigrationStatus struct {
	Version int
	Name    string
	Applied bool
}

// Reset 重置数据库（危险操作，仅用于开发环境）
func (m *Migrator) Reset() error {
	// 删除所有表
	query := `
		DROP SCHEMA IF EXISTS public CASCADE;
		CREATE SCHEMA public;
		GRANT ALL ON SCHEMA public TO public;
	`
	_, err := m.db.Exec(query)
	if err != nil {
		return fmt.Errorf("failed to reset database: %w", err)
	}

	fmt.Println("Database reset completed")
	return nil
}
